<?php
jimport('joomla.application.component.model');
class svModelUseraccount extends JModel
{
	//------------ tim kiem----------------------------------
	function getAllAccount($param)
	{
		$clause='';
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $clause=$clause.'  WHERE pb.pb_iddonviquanly='.$donvi.' ';
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $clause=$clause.' WHERE qlc.qlc_idphongban='.$phongban.' ';
		$canbo=JRequest::getInt('sn_idcanbo');
		if($canbo!=0) $clause=$clause.'  WHERE cb.cb_id='.$canbo.' ';
		//$orderby=' ORDER BY '.$param['order'].'  '.$param['order_Dir'].';
		$limit='LIMIT '.$param['limitstart'].' , '.$param['limit'];
		$query = 'SELECT a.*, g.name AS groupname,ac.*,cb.cb_ten,pb.pb_ten'
		. ' FROM #__users AS a'
		. ' INNER JOIN #__core_acl_aro AS aro ON aro.value = a.id'
		. ' INNER JOIN #__core_acl_groups_aro_map AS gm ON gm.aro_id = aro.id'
		. ' INNER JOIN #__core_acl_aro_groups AS g ON g.id = gm.group_id'
		. ' INNER JOIN qlcb_useraccount AS ac ON ac.ac_iduser = a.id'
		. ' INNER JOIN qlcb_canbo AS cb ON cb.cb_id = ac.ac_idcanbo'
		. ' INNER JOIN qlcb_quanlychung AS qlc ON qlc.qlc_idcanbo = cb.cb_id'
		. ' INNER JOIN qlcb_phongban AS pb ON pb.pb_id = qlc.qlc_idphongban'
		. $clause
		. ' GROUP BY a.id DESC '
		. $limit
		;
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getTotal()
	{
		$clause='';
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $clause=$clause.'  WHERE pb.pb_iddonviquanly='.$donvi.' ';
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $clause=$clause.' WHERE qlc.qlc_idphongban='.$phongban.' ';
		$canbo=JRequest::getInt('cb_id');
		if($canbo!=0) $clause=$clause.'  WHERE cb.cb_id='.$canbo.' ';
		$filter='';
		$where='';
		$query = 'SELECT COUNT(a.id)'
		. ' FROM #__users AS a'
		. ' INNER JOIN #__core_acl_aro AS aro ON aro.value = a.id'
		. ' INNER JOIN #__core_acl_groups_aro_map AS gm ON gm.aro_id = aro.id'
		. ' INNER JOIN #__core_acl_aro_groups AS g ON g.id = gm.group_id'
		. ' INNER JOIN qlcb_useraccount AS ac ON ac.ac_iduser = a.id'
		. ' INNER JOIN qlcb_canbo AS cb ON cb.cb_id = ac.ac_idcanbo'
		. ' INNER JOIN qlcb_quanlychung AS qlc ON qlc.qlc_idcanbo = cb.cb_id'
		. ' INNER JOIN qlcb_phongban AS pb ON pb.pb_id = qlc.qlc_idphongban'
		. $clause
		. ' '
		;
		/*	$query='SELECT 	 acc.*,cb.cb_ten,pb.pb_ten
		 FROM	 qlcb_account as acc,qlcb_canbo as cb,qlcb_phongban as pb,qlcb_quanlychung as qlc
		WHERE	 acc.ac_idcanbo=cb.cb_id
		AND		qlc.qlc_idcanbo=cb.cb_id
		AND		qlc.qlc_idphongban=pb.pb_id
		';*/
		$result=$this->_loadResult($query);
		return $result;
	}
	function getAccount()
	{
		$query='SELECT 	 acc.*,cb.cb_ten,pb.pb_ten
		FROM	 qlcb_account as acc,qlcb_canbo as cb,qlcb_phongban as pb,qlcb_quanlychung as qlc
		WHERE	 acc.ac_idcanbo=cb.cb_id
		AND		qlc.qlc_idcanbo=cb.cb_id
		AND		qlc.qlc_idphongban=pb.pb_id
		ORDER BY acc.ac_id  DESC  
		LIMIT	1 ';
		$result=$this->_loadObject($query);
		return $result;
	}
	function getAccountByID($idaccount)
	{
		 $query = 'SELECT a.*, g.name AS groupname,ac.*,cb.cb_ten,pb.pb_ten'
		. ' FROM #__users AS a'
		. ' INNER JOIN #__core_acl_aro AS aro ON aro.value = a.id'
		. ' INNER JOIN #__core_acl_groups_aro_map AS gm ON gm.aro_id = aro.id'
		. ' INNER JOIN #__core_acl_aro_groups AS g ON g.id = gm.group_id'
		. ' INNER JOIN qlcb_useraccount AS ac ON ac.ac_iduser = a.id'
		. ' INNER JOIN qlcb_canbo AS cb ON cb.cb_id = ac.ac_idcanbo'
		. ' INNER JOIN qlcb_quanlychung AS qlc ON qlc.qlc_idcanbo = cb.cb_id'
		. ' INNER JOIN qlcb_phongban AS pb ON pb.pb_id = qlc.qlc_idphongban'
		.'  WHERE a.id='.$idaccount;
		;
		$result=$this->_loadObject($query);
		return $result;
	}
	function Listcanbo()
	{
		$where="";
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $where=',qlcb_quanlychung as qlc,qlcb_phongban as pb
		WHERE qlc.qlc_idcanbo = cb.cb_id
		AND  qlc.qlc_idphongban = pb.pb_id
		AND pb.pb_iddonviquanly='.$donvi;
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $where=',qlcb_quanlychung as qlc WHERE qlc.qlc_idcanbo=cb.cb_id AND qlc.qlc_idphongban='.$phongban;
		$query='SELECT DISTINCT cb.cb_id as `key`,cb.cb_ten as `text`  FROM qlcb_canbo as cb '.$where;
		return $this->_getList($query);
	}
	function ListPhongban()
	{
		$where="";
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $where=' WHERE pb_iddonviquanly='.$donvi;
		$query='SELECT DISTINCT pb_id as `key`,pb_ten as `text`  FROM qlcb_phongban '.$where;
		return $this->_getList($query);
	}
	function ListDonvi()
	{
		$query='SELECT DISTINCT dvql_id as `key`,dvql_ten as `text`  FROM qlcb_donviquanly';
		return $this->_getList($query);
	}
	//----------------------------them ,sua ,xoa -------------------------------
	function addAccount($ac)
	{
		$this->addjos_core_acl_groups_aro_map($ac);
		$this->addjos_core_acl_aro($ac);
		$this->addUserAccount($ac);
		$this->addOrigineAccount($ac);
	}
	function editAccount($ac)
	{

		$this->editjos_core_acl_groups_aro_map($ac);
		$this->editUserAccount($ac);
		$this->editOrigineAccount($ac);
	}
	function addjos_core_acl_groups_aro_map($ac)
	{
		$maxquery='SELECT MAX(id) from jos_core_acl_aro';
		$max=$this->_loadResult($maxquery)+1;
		//-------------------
		$query="INSERT INTO jos_core_acl_groups_aro_map(group_id,section_value, aro_id)
		VALUES('".$ac['gid']."','','".$max."')";
		$message='Thêm mới ';
		$result=$this->_Execute($query, $message);
		return 1;
	}
	function editjos_core_acl_groups_aro_map($ac)
	{
		$maxquery='SELECT id from jos_core_acl_aro WHERE value='.$ac['idaccount'];
		$max=$this->_loadResult($maxquery);
		//-------------------
		echo $query="UPDATE jos_core_acl_groups_aro_map SET group_id='".$ac['gid']."' WHERE  aro_id='".$max."' ";
		$message='Thêm mới ';
		$result=$this->_Execute($query, $message);
		return 1;
	}
	function addjos_core_acl_aro($ac)
	{
		// lay du lieu
		$name=$this->selectName($ac['canbo']);
		$iduser=$this->getMaxIDuser()+1;
		//-------------------
		$query="INSERT INTO jos_core_acl_aro(section_value,value, order_value,name,hidden)
		VALUES('users','".$iduser."','0','".$name."','0')";
		$message='Thêm mới ';
		$result=$this->_Execute($query, $message);
		return 1;
	}
	function addUserAccount($ac)
	{
		// lay du lieu
		$iduser=$this->getMaxIDuser()+1;
		//-------------------
		$query="INSERT INTO qlcb_useraccount(ac_idcanbo,ac_iduser,ac_typeaccount)
		VALUES('".$ac['canbo']."','".$iduser."','".$ac['quyen']."')";
		$message='Thêm mới ';
		$result=$this->_Execute($query, $message);
		return 1;
	}
	function editUserAccount($ac)
	{
		// lay du lieu
		$iduser=$this->getMaxIDuser()+1;
		//-------------------
		echo $query="UPDATE qlcb_useraccount SET ac_typeaccount='".$ac['quyen']."' WHERE ac_iduser='".$ac['idaccount']."'";
		$message='Thêm mới ';
		$result=$this->_Execute($query, $message);
		return 1;
	}
	function addOrigineAccount($ac)
	{
		// lay du lieu
		$name=$this->selectName($ac['canbo']);
		$usertype='';
		$gid=$ac['gid'];
		switch ($gid)
		{
			case '29': 
				$usertype='Public Front-end';
				break;
			case '18':
				$usertype='Registered';
				break;
			case '19':
				$usertype='Author';
				break;
			case '20':
				$usertype='Editor';
				break;
			case '21':
				$usertype='Publisher';
				break;
			case '30':
				$usertype='Public Back-end';
				break;
			case '23':
				$usertype='Manager';
				break;
			case '24':
				$usertype='Administrator';
				break;
			case '25':
				$usertype='Super Administrator';
				break;
			default : break;
		}
		$ac['dateDangki']=JFactory::getDate()->toMysql();
		// ma hoa md5 password
		jimport( 'joomla.user.helper' );
		$salt = JUserHelper::genRandomPassword(32);
		$crypt = JUserHelper::getCryptedPassword($ac['password'], $salt);
		$pass = $crypt.':'.$salt;
		//-------------------
		$dateDangki=JFactory::getDate()->toMysql();
		//-------------------
		$query="INSERT INTO jos_users(name,username,email,password,usertype,block,sendEmail,gid,registerDate,lastvisitDate)
		VALUES('".$name."','".$ac['username']."','".$ac['email']."','".$pass."','".$usertype."'
		,'".$ac['block']."','".$ac['sendEmail']."','".$ac['gid']."','".$ac['dateDangki']."','".$ac['dateUpdate']."')";
		$message='Thêm mới ';
		$result=$this->_Execute($query, $message);
		return 1;
	}
	function editOrigineAccount($ac)
	{
		// lay du lieu
		$name=$this->selectName($ac['canbo']);
		$usertype='';
		$gid=$ac['gid'];
		switch ($gid)
		{
			case '29':
				$usertype='Public Front-end';
				break;
			case '18':
				$usertype='Registered';
				break;
			case '19':
				$usertype='Author';
				break;
			case '20':
				$usertype='Editor';
				break;
			case '21':
				$usertype='Publisher';
				break;
			case '30':
				$usertype='Public Back-end';
				break;
			case '23':
				$usertype='Manager';
				break;
			case '24':
				$usertype='Administrator';
				break;
			case '25':
				$usertype='Super Administrator';
				break;
			default : break;
		}
		$ac['dateUpdate']=JFactory::getDate()->toMysql();
		// ma hoa md5 password
		jimport( 'joomla.user.helper' );
		$salt = JUserHelper::genRandomPassword(32);
		$crypt = JUserHelper::getCryptedPassword($ac['password'], $salt);
		$pass = $crypt.':'.$salt;
		$alloww=" ";
		if($ac['password']!='') $alloww=" password='".$pass."', ";

		//-------------------
		$dateDangki=JFactory::getDate()->toMysql();
		//-------------------
		echo $query="UPDATE jos_users SET name='".$name."' ,username='".$ac['username']."',email='".$ac['email']."',
		 ".$alloww." usertype='".$usertype."',block='".$ac['block']."'
		,sendEmail='".$ac['sendEmail']."',gid='".$ac['gid']."',lastvisitDate='".$ac['dateUpdate']."'
		  WHERE id=".$ac['idaccount'];
		$message='Thêm mới ';
		$result=$this->_Execute($query, $message);
		return 1;
	}
	function getMaxIDuser()
	{
		$query='SELECT MAX(id) FROM jos_users';
		$result=$this->_loadResult($query);
		return $result;
	}
	function selectName($id)
	{
		$query='SELECT cb_ten FROM qlcb_canbo WHERE cb_id='.$id;
		$result=$this->_loadResult($query);
		return $result;
	}
	// sua
	function editAccountt($idaccount,$idcanbo,$username,$password,$email,$typeaccount)
	{
		$query="UPDATE qlcb_account SET ac_idcanbo='".$idcanbo."',ac_username='".$username."'
									,ac_email='".$email."',ac_password='".$password."',ac_typeaccount='".$typeaccount."'
	   		    WHERE ac_id=".$idaccount;
		$message='Chỉnh sửa ';
		$result=$this->_Execute($query, $message);
		return $result.' <b style="font-size:18px;color:blue;">'.$ten.'</b>';
	}
	function delAccount($idaccount)
	{
		$query="DELETE FROM qlcb_account WHERE ac_id=".$idaccount;
		$message='Xóa 1 item ';
		$result=$this->_Execute($query, $message);
		return '<b style="font-size:18px;color:blue;">'.$result.'</b>';	
	}
	// cac ham phuc vu truy van CSDL
	function _loadResult($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadResult();
		return $result;
	}
	function _loadObject($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadObject();
		return $result;
	}
	function _loadObjectList($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadObjectList();
		return $result;
	}
	function _Execute($query,$message)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		try {
			$db->query();
			return $message." thành công ";
		}
		catch (Exception $e){
			return $message." Không thành công! Vui lòng thử lại.";
		}
	}
}
?>